package cn.jjxx.modules.test.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializeFilter;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import cn.jjxx.core.common.controller.BaseBeanController;
import cn.jjxx.core.common.data.DuplicateValid;
import cn.jjxx.core.model.AjaxJson;
import cn.jjxx.core.model.PageJson;
import cn.jjxx.core.model.ValidJson;
import cn.jjxx.core.query.annotation.PageableDefaults;
import cn.jjxx.core.query.data.PropertyPreFilterable;
import cn.jjxx.core.query.data.Queryable;
import cn.jjxx.core.query.utils.QueryableConvertUtils;
import cn.jjxx.core.query.wrapper.EntityWrapper;
import cn.jjxx.core.security.shiro.authz.annotation.RequiresMethodPermissions;
import cn.jjxx.core.security.shiro.authz.annotation.RequiresPathPermission;
import cn.jjxx.core.utils.ObjectUtils;
import cn.jjxx.core.utils.StringUtils;
import cn.jjxx.modules.test.entity.CommonReport;
import cn.jjxx.modules.test.entity.SingleTable;
import cn.jjxx.modules.test.service.ISingleTableService;

/**   
 * @Title: 测试单表
 * @Description: 测试单表
 * @author jeeweb
 * @date 2017-09-10 15:02:18
 * @version V1.0   
 *
 */
@Controller
@RequestMapping("${admin.url.prefix}/test/singletable")
@RequiresPathPermission("test:singletable")
public class SingleTableController extends BaseBeanController<SingleTable> {

    @Autowired
    protected ISingleTableService singleTableService;

    public SingleTable get(String id) {
        if (!ObjectUtils.isNullOrEmpty(id)) {
            return singleTableService.selectById(id);
        } else {
            return newModel();
        }
    }

    @RequiresMethodPermissions("list")
    @RequestMapping(method = RequestMethod.GET)
    public String list(Model model, HttpServletRequest request, HttpServletResponse response) {
        return display("list");
    }

    @RequestMapping(value = "ajaxList", method = { RequestMethod.GET, RequestMethod.POST })
    @PageableDefaults(sort = "id=desc")
    private void ajaxList(Queryable queryable, PropertyPreFilterable propertyPreFilterable, HttpServletRequest request,
                          HttpServletResponse response) throws IOException {
        EntityWrapper<SingleTable> entityWrapper = new EntityWrapper<SingleTable>(entityClass);
        propertyPreFilterable.addQueryProperty("id");
        // 预处理
        QueryableConvertUtils.convertQueryValueToEntityValue(queryable, entityClass);
        SerializeFilter filter = propertyPreFilterable.constructFilter(entityClass);
        PageJson<SingleTable> pagejson = new PageJson<SingleTable>(singleTableService.list(queryable,entityWrapper));
        String content = JSON.toJSONString(pagejson, filter);
        StringUtils.printJson(response, content);
        
//        testOracle();
    }
    
    public List<CommonReport> linkOracle(String sql)
    {
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象
        List<CommonReport> list = new ArrayList<>();
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
            System.out.println("开始尝试连接数据库！");
            String url = "jdbc:oracle:thin:@139.9.229.217:1521:orcl";// 127.0.0.1是本机地址，XE是精简版Oracle的默认数据库名
            String user = "gzlqjtgs";// 用户名,系统默认的账户名
            String password = "GZlq_2019";// 你安装时选设置的密码
            con = DriverManager.getConnection(url, user, password);// 获取连接
            System.out.println("连接成功！");
            
            

            pre = con.prepareStatement(sql);// 实例化预编译语句
            result = pre.executeQuery();// 执行查询，注意括号中不需要再加参数
            
            while (result.next()){
        		CommonReport temp = new CommonReport();
        		temp.setName(result.getString("GSMC"));
        		temp.setOrgLong(result.getString("GSBM"));
        		temp.setBy1(result.getString("BYJHCZ"));
        		temp.setBy2(result.getString("BYWCCZ"));
        		temp.setBy3(result.getString("BYZRCB"));
        		temp.setBy4(result.getString("BYSJCB"));
        		temp.setBy5(result.getString("BYJYLR"));
        		temp.setBy6(result.getString("BYGLLR"));
        		temp.setBy7(result.getString("BYSJLR"));
        		String a = result.getString("BYCZMLL");
        		if(result.getString("BYCZMLL") == null || "".equals(result.getString("BYCZMLL"))){
        			temp.setBy8("0.00");
        		}else{
        			temp.setBy8(result.getBigDecimal("BYCZMLL").toString());
        		}
        		
        		
        		temp.setBn1(result.getString("BNJHCZ"));
        		temp.setBn2(result.getString("BNWCCZ"));
        		temp.setBn3(result.getString("BNZRCB"));
        		temp.setBn4(result.getString("BNSJCB"));
        		temp.setBn5(result.getString("BNJYLR"));
        		temp.setBn6(result.getString("BNGLLR"));
        		temp.setBn7(result.getString("BNSJLR"));
        		if(result.getString("BNCZMLL") == null || "".equals(result.getString("BNCZMLL"))){
        			temp.setBn8("0.00");
        		}else{
        			temp.setBn8(result.getBigDecimal("BNCZMLL").toString());
        		}
        		
        		
        		temp.setKl1(result.getString("KLJHCZ"));
        		temp.setKl2(result.getString("KLWCCZ"));
        		temp.setKl3(result.getString("KLZRCB"));
        		temp.setKl4(result.getString("KLSJCB"));
        		temp.setKl5(result.getString("KLJYLR"));
        		temp.setKl6(result.getString("KLGLLR"));
        		temp.setKl7(result.getString("KLSJLR"));
        		if(result.getString("KLCZMLL") == null || "".equals(result.getString("KLCZMLL"))){
        			temp.setKl8("0.00");
        		}else{
        			temp.setKl8(result.getBigDecimal("KLCZMLL").toString());
        		}
        		
        		list.add(temp);
        		
        	}
            
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
                // 注意关闭的顺序，最后使用的最先关闭
                if (result != null)
                    result.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
                System.out.println("数据库连接已关闭！");
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
        }
		return list;
    }
    
    
    @RequestMapping(value = "toReport", method = RequestMethod.GET)
    public String toReport(Model model, HttpServletRequest request, HttpServletResponse response) {
        
        return display("toReport");
        
    }
    
    @RequestMapping(value = "toProjectList", method = RequestMethod.GET)
    public String toProjectList(Model model, HttpServletRequest request, HttpServletResponse response) {
    	model.addAttribute("orgLong",request.getParameter("orgLong"));
        return display("toProjectList"); 
        
    }
    
    /**
    *
    * @Description:查询报表统计数据
    * @author: jinjingwei
    */
    @RequestMapping(value = "getMainEcharts", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public Map<String, Object> getMainEcharts(Model model, HttpServletRequest request, HttpServletResponse response) {
        Map<String, Object> map = new HashMap<>();
        List<CommonReport> list = new ArrayList<>();
        try {
        	String orgNum = request.getParameter("orgNum");
        	String type = request.getParameter("type");
        	
        	String periodSelect = request.getParameter("periodSelect");
        	String typeSelect = request.getParameter("typeSelect");
        	String sortName = periodSelect.concat(typeSelect);
        	ResultSet result = null;
        	if(orgNum != null && !"".equals(orgNum) && type != null && !"".equals(type)){
        		String sql = splitSql(orgNum,type,sortName);
        		System.out.println(sql);
        		list = linkOracle(sql);
        	}
        	        	
        	map.put("list", list);
        	map.put("type", type);
        	

        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }
    
    public String splitSql(String orgNum,String type,String sortName){
    	String sql = null;
    	String num = "0";
    	if("1".equals(type)){
    		num = "4";
    		orgNum = orgNum + "%";
    	}else if("2".equals(type)){
    		num = "5";
    		orgNum = "%" + orgNum + "%";
    	}
    	
    	sql = "/*dialect*/ select gsbm,gsmc,\n"+
    			"round(sum(byjhcz)* 0.0001,2) byjhcz,\n"+
    			"round(sum(bywccz)* 0.0001,2) bywccz,\n"+
    			"round(sum(byzrcb)* 0.0001,2) byzrcb,\n"+
    			"round(sum(bysjcb)* 0.0001,2) bysjcb,\n"+
    			"round((sum(bywccz)-sum(byzrcb))* 0.0001,2) byjylr,\n"+
    			"round(sum(bygllr)* 0.0001,2) bygllr,\n"+
    			"round((sum(bywccz)-sum(bysjcb))* 0.0001,2) bysjlr,\n"+
    			"case when round((sum(bywccz)-sum(bysjcb) )/ nullif(sum(bywccz),0),2) is null then 0 else round((sum(bywccz)-sum(bysjcb) )/ nullif(sum(bywccz),0),2) end byczmll,\n"+

    			"round(sum(bnjhcz)* 0.0001,2) bnjhcz,\n"+
    			"round(sum(bnwccz)* 0.0001,2) bnwccz,\n"+
    			"round(sum(bnzrcb)* 0.0001,2) bnzrcb,\n"+
    			"round(sum(bnsjcb)* 0.0001,2) bnsjcb,\n"+
    			"round((sum(bnwccz)-sum(bnzrcb))* 0.0001,2) bnjylr,\n"+
    			"round(sum(bngllr)* 0.0001,2) bngllr,\n"+
    			"round((sum(bnwccz)-sum(bnsjcb))* 0.0001,2) bnsjlr,\n"+
    			"case when round((sum(bnwccz)-sum(bnsjcb) )/ nullif(sum(bnwccz),0),2) is null then 0 else round((sum(bnwccz)-sum(bnsjcb) )/ nullif(sum(bnwccz),0),2) end bnczmll,\n"+

    			"round(sum(kljhcz)* 0.0001,2) kljhcz,\n"+
    			"round(sum(klwccz)* 0.0001,2) klwccz,\n"+
    			"round(sum(klzrcb)* 0.0001,2) klzrcb,\n"+
    			"round(sum(klsjcb)* 0.0001,2) klsjcb,\n"+
    			"round((sum(klwccz)-sum(klzrcb))* 0.0001,2) kljylr,\n"+
    			"round(sum(klgllr)* 0.0001,2) klgllr,\n"+
    			"round((sum(klwccz)-sum(klsjcb))* 0.0001,2) klsjlr,\n"+
    			"case when round((sum(klwccz)-sum(klsjcb) )/ nullif(sum(klwccz),0),2) is null then 0 else round((sum(klwccz)-sum(klsjcb) )/ nullif(sum(klwccz),0),2) end klczmll\n"+

    			"from (\n"+

    			"select \n"+
    			"Get_StrArrayStrOfIndex(org.FLongNumber,"+num+",'!') as gsbm,\n"+
    			"NVL((select FSimpleName from T_ORG_BaseUnit where FNumber =Get_StrArrayStrOfIndex(org.FLongNumber,"+num+",'!')) ,Get_StrArrayStrOfIndex(org.FDisplayName_L2,"+num+",'_')) as gsmc,\n"+
    			"NVL(pj.FSimpleName ,pj.FName_l2) as gcxm ,\n"+
    			"--本月\n"+
    			"NVL((select sum(pcce.FPlanAmount) from T_EC_PdValuePlanBillEntry pcce left join T_EC_PdValuePlanBill pcc on pcce.FParentID =pcc.fid where  pj.FID = pcc.FProjectID and pcc.FBillSate=03 and \n"+"pcc.FProcessType=03 and pcc.FPeriodID =(select dp.FID from T_BD_Period dp where (dp.Fnumber='202007'))),0) as byjhcz,\n"+
    			"NVL((select sum(pcce.FCurProduction) from T_EC_ProjectConfirmCbsEntry pcce,T_EC_ProjectConfirmCbsBill pcc where pcce.FParentID =pcc.FID and pj.FID = pcc.FProjectID and pcc.FBillSate=03 \n"+"and pcc.FPeriodID =(select dp.FID from T_BD_Period dp where (dp.Fnumber='202007'))),0) as bywccz,\n"+
    			"NVL((NVL(RespCostSummary1.zjgcf,0)+ NVL(RespCostSummary2.qtgcf,0)+NVL(RespCostSummary3.qtgcf,0)+NVL(RespCostSummary4.xmgllr,0)+NVL(RespCostSummary5.bcp,0)+\n"+
    			"NVL(RespCostSummary6.cltc,0)+NVL(RespCostSummary7.gcsj,0)),0) as byzrcb,\n"+
    			"NVL(RealDirect1.lwf,0)+NVL(RealDirect2.clf,0)+NVL(RealDirect3.jxf,0)+NVL(RealOtherBill.qtgcf,0)+NVL(RealInDirect.jjf,0)+NVL(zhoucaifei.zcf,0)+\n"+
    			"NVL(RealOtherBill4.gcsj,0)+NVL(diangongdan.qtf,0)+NVL(jiabocaicha.jbcc,0) as bysjcb,\n"+
    			"NVL((((NVL(RespCostSummary1.zjgcf,0)+ NVL(RespCostSummary2.qtgcf,0)+NVL(RespCostSummary3.qtgcf,0)+NVL(RespCostSummary4.xmgllr,0)+NVL(RespCostSummary5.bcp,0)+\n"+
    			"NVL(RespCostSummary6.cltc,0)+NVL(RespCostSummary7.gcsj,0)))-(NVL(RealDirect1.lwf,0)+NVL(RealDirect2.clf,0)+NVL(RealDirect3.jxf,0)+NVL(RealOtherBill.qtgcf,0)+NVL(RealInDirect.jjf,0)+\n"+
    			"NVL(zhoucaifei.zcf,0)+NVL(RealOtherBill4.gcsj,0)+NVL(diangongdan.qtf,0)+NVL(jiabocaicha.jbcc,0))),0) as bygllr,\n"+
    			"--本年\n"+
    			"NVL((select sum(pcce.FPlanAmount) from T_EC_PdValuePlanBillEntry pcce left join T_EC_PdValuePlanBill pcc on pcce.FParentID =pcc.fid where  pj.FID = pcc.FProjectID and pcc.FBillSate=03 and \n"+
    			"pcc.FProcessType=03 and pcc.FPeriodID in(select dp.FID from T_BD_Period dp where (dp.FPeriodYear=substr('202007',1,4) and dp.Fnumber<='202007'))),0) as bnjhcz,\n"+
    			"NVL((select sum(pcce.FCurProduction) from T_EC_ProjectConfirmCbsEntry pcce,T_EC_ProjectConfirmCbsBill pcc  where pcce.FParentID =  pcc.FID and pj.FID = pcc.FProjectID and pcc.FBillSate=03\n"+ 
    			"and pcc.FPeriodID in(select dp.FID from T_BD_Period dp where (dp.FPeriodYear=substr('202007',1,4) and dp.Fnumber<='202007'))),0) as bnwccz,\n"+
    			"NVL((NVL(yRespCostSummary1.zjgcf,0)+ NVL(yRespCostSummary2.qtgcf,0)+NVL(yRespCostSummary3.qtgcf,0)+NVL(yRespCostSummary4.xmgllr,0)+NVL(yRespCostSummary5.bcp,0)+\n"+
    			"NVL(yRespCostSummary6.cltc,0)+NVL(yRespCostSummary7.gcsj,0)),0) as bnzrcb,\n"+
    			"NVL(yRealDirect1.lwf,0)+NVL(yRealDirect2.clf,0)+NVL(yRealDirect3.jxf,0)+NVL(yRealOtherBill.qtgcf,0)+NVL(yRealInDirect.jjf,0)+NVL(yzhoucaifei.zcf,0)+NVL(yRealOtherBill4.gcsj,0)+\n"+
    			"NVL(ydiangongdan.qtf,0)+NVL(yjiabocaicha.jbcc,0) as bnsjcb,\n"+
    			"NVL((((NVL(yRespCostSummary1.zjgcf,0)+ NVL(yRespCostSummary2.qtgcf,0)+NVL(yRespCostSummary3.qtgcf,0)+NVL(yRespCostSummary4.xmgllr,0)+NVL(yRespCostSummary5.bcp,0)+\n"+
    			"NVL(yRespCostSummary6.cltc,0)+NVL(yRespCostSummary7.gcsj,0)))-(NVL(yRealDirect1.lwf,0)+NVL(yRealDirect2.clf,0)+NVL(yRealDirect3.jxf,0)+NVL(yRealOtherBill.qtgcf,0)+\n"+
    			"NVL(yRealInDirect.jjf,0)+NVL(yzhoucaifei.zcf,0)+NVL(yRealOtherBill4.gcsj,0)+NVL(ydiangongdan.qtf,0)+NVL(yjiabocaicha.jbcc,0))),0) as bngllr,\n"+
    			"--开累\n"+
    			"NVL((select sum(pcce.FPlanAmount) from T_EC_PdValuePlanBillEntry pcce left join T_EC_PdValuePlanBill pcc on pcce.FParentID =pcc.fid where  pj.FID = pcc.FProjectID and pcc.FBillSate=03 and\n"+ 
    			"pcc.FProcessType=03 and pcc.FPeriodID in(select dp.FID from T_BD_Period dp where (dp.FPeriodYear<=substr('202007',1,4) and dp.Fnumber<='202007'))),0) as kljhcz,\n"+
    			"NVL((select sum(pcce.FCurProduction) from T_EC_ProjectConfirmCbsEntry pcce  left join T_EC_ProjectConfirmCbsBill pcc on pcc.fid=pcce.FParentID  where  pcc.FBillSate=03 and pcc.FPeriodID in\n"+
    			"(select dp.FID from T_BD_Period dp where (dp.FPeriodYear<=substr('202007',1,4) and dp.Fnumber<='202007')) and pj.FID = pcc.FProjectID ),0) as klwccz,\n"+
    			"NVL((NVL(kRespCostSummary1.zjgcf,0)+ NVL(kRespCostSummary2.qtgcf,0)+NVL(kRespCostSummary3.qtgcf,0)+NVL(kRespCostSummary4.xmgllr,0)+NVL(kRespCostSummary5.bcp,0)+\n"+
    			"NVL(kRespCostSummary6.cltc,0)+NVL(kRespCostSummary7.gcsj,0)),0) as klzrcb,\n"+
    			"NVL(kRealDirect1.lwf,0)+NVL(kRealDirect2.clf,0)+NVL(kRealDirect3.jxf,0)+NVL(kRealOtherBill.qtgcf,0)+NVL(kRealInDirect.jjf,0)+NVL(kzhoucaifei.zcf,0)+NVL(kRealOtherBill4.gcsj,0)+\n"+
    			"NVL(kdiangongdan.qtf,0)+NVL(kjiabocaicha.jbcc,0) as klsjcb,\n"+
    			"NVL((((NVL(kRespCostSummary1.zjgcf,0)+ NVL(kRespCostSummary2.qtgcf,0)+NVL(kRespCostSummary3.qtgcf,0)+NVL(kRespCostSummary4.xmgllr,0)+NVL(kRespCostSummary5.bcp,0)+NVL(kRespCostSummary6.cltc,0)+NVL(kRespCostSummary7.gcsj,0)))-(NVL(kRealDirect1.lwf,0)+NVL(kRealDirect2.clf,0)+NVL(kRealDirect3.jxf,0)+NVL(kRealOtherBill.qtgcf,0)+NVL(kRealInDirect.jjf,0)+\n"+
    			"NVL(kzhoucaifei.zcf,0)+NVL(kRealOtherBill4.gcsj,0)+NVL(kdiangongdan.qtf,0)+NVL(kjiabocaicha.jbcc,0))),0) as klgllr\n"+
    			"from T_EC_Project pj\n"+
    			"left join T_ORG_BaseUnit org on pj.FProjectOrgID =org.FID\n"+
    			"left join \n"+
    			"(select sum(FORIGINALAMOUNT) as zjgcf,FProjectID from T_PER_DirectRespCost\n"+
    			"inner join T_BD_Period on T_PER_DirectRespCost.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber='202007' and FBillSate='03'\n"+
    			"group by FProjectID)RespCostSummary1\n"+
    			"on RespCostSummary1.FProjectID=pj.FID\n"+
    			"left join \n"+
    			"(select sum(FORIGINALAMOUNT) as qtgcf,FProjectID from T_PER_PeriodCostBill\n"+
    			"inner join T_BD_Period on T_PER_PeriodCostBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber='202007' and FBillSate='03'\n"+
    			"group by FProjectID)RespCostSummary2\n"+
    			"on RespCostSummary2.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FORIGINALAMOUNT) as qtgcf,FProjectID from T_PER_IndirectCostBill\n"+
    			"inner join T_BD_Period on T_PER_IndirectCostBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber='202007' and FBillSate='03'\n"+
    			"group by FProjectID)RespCostSummary3\n"+
    			"on RespCostSummary3.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FCostAmount) as xmgllr ,FProjectID from T_PER_RespCostSumEntry inner join T_PER_RespCostSummary \n"+
    			"on T_PER_RespCostSummary.FID=T_PER_RespCostSumEntry.FBillID\n"+
    			"inner join T_BD_Period on T_PER_RespCostSummary.FPeriodID=T_BD_Period.FID\n"+
    			"where T_PER_RespCostSumEntry.FNumber='项目管理利润' and\n"+
    			" T_BD_Period.Fnumber='202007' and FBillSate='03'\n"+
    			"group by FProjectID)RespCostSummary4\n"+
    			"on RespCostSummary4.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(T_EC_ProjectFillCbsSemiEntry.FAMOUNT) as bcp ,T_EC_ProjectFillCbsBill.FProjectID from  T_EC_ProjectFillCbsSemiEntry inner join T_EC_ProjectFillCbsBill  \n"+
    			"         on T_EC_ProjectFillCbsBill.FID=T_EC_ProjectFillCbsSemiEntry.fparentid \n"+
    			"        inner join T_BD_Period on T_EC_ProjectFillCbsBill.FPeriodID=T_BD_Period.FID \n"+
    			"where \n"+
    			" T_BD_Period.Fnumber='202007' and T_EC_ProjectFillCbsBill.FBillSate='03'\n"+
    			"group by T_EC_ProjectFillCbsBill.FProjectID)RespCostSummary5\n"+
    			"on RespCostSummary5.FProjectID=pj.FID\n"+
    			"left join \n"+
    			"(select sum(fdifamount) as cltc ,FProjectID from T_EC_MaterialGapBillEntry inner join T_EC_MaterialGapBill \n"+
    			"on T_EC_MaterialGapBill.FID=T_EC_MaterialGapBillEntry.fparentid\n"+
    			"inner join T_BD_Period on T_EC_MaterialGapBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber='202007' and T_EC_MaterialGapBill.fbillsate=3\n"+
    			"group by FProjectID)RespCostSummary6\n"+
    			"on RespCostSummary6.FProjectID=pj.FID\n"+
    			"left join \n"+
    			"(select sum(FORIGINALAMOUNT) as gcsj,FProjectID from T_PER_RespCostEngineerTax\n"+
    			"inner join T_BD_Period on T_PER_RespCostEngineerTax.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber='202007' and FBillSate='03'\n"+
    			"group by FProjectID)RespCostSummary7\n"+
    			"on RespCostSummary7.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FHumanAmount) as lwf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber = '202007' and FBillSate='03'\n"+
    			"group by FProjectID)RealDirect1\n"+
    			"on RealDirect1.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FMaterialAmount)+SUM(FSpordicAmount) as clf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber = '202007' and FBillSate='03'\n"+
    			"group by FProjectID)RealDirect2\n"+
    			"on pj.FID=RealDirect2.FProjectID\n"+
    			"left join\n"+
    			"(select SUM(FEquipmentAmount) as jxf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber = '202007' and FBillSate='03'\n"+
    			"group by FProjectID)RealDirect3\n"+
    			"on RealDirect3.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FOriginalAmount) as qtgcf,FProjectID from T_EC_RealOtherBill\n"+
    			"inner join T_BD_Period on T_EC_RealOtherBill.FPeriodID=T_BD_Period.fid\n"+
    			"where T_BD_Period.Fnumber = '202007' and FBillSate='03'\n"+
    			"group by FProjectID\n"+
    			")RealOtherBill\n"+
    			"on RealOtherBill.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FTaxation) as gcsj,FProjectID from T_EC_RealTaxBill\n"+
    			"inner join T_BD_Period on T_EC_RealTaxBill.FPeriodID=T_BD_Period.fid\n"+
    			"where T_BD_Period.Fnumber = '202007' and FBillSate='03'\n"+
    			" group by FProjectID\n"+
    			")RealOtherBill4\n"+
    			"on RealOtherBill4.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FTurnAmount) as zcf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber = '202007' and FBillSate='03'\n"+
    			"group by FProjectID)zhoucaifei\n"+
    			"on zhoucaifei.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FExpenseAmount) as qtf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber = '202007' and FBillSate='03'\n"+
    			"group by FProjectID)diangongdan\n"+
    			"on diangongdan.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FOriginalAmount) as jjf,FProjectID from T_EC_RealInDirectBill\n"+
    			"inner join T_BD_Period on T_EC_RealInDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber = '202007' and FBillSate='03'\n"+
    			"group by FProjectID)RealInDirect\n"+
    			"on pj.FID=RealInDirect.FProjectID\n"+
    			"left join\n"+
    			"(select SUM(fsubprice) as jbcc,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber = '202007' and FBillSate='03'\n"+
    			"group by FProjectID)jiabocaicha\n"+
    			"on jiabocaicha.FProjectID=pj.FID\n"+
    			"left join \n"+
    			"(select sum(FORIGINALAMOUNT) as zjgcf,FProjectID from T_PER_DirectRespCost\n"+
    			"inner join T_BD_Period on T_PER_DirectRespCost.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007' and FBillSate='03'\n"+
    			"group by FProjectID)yRespCostSummary1\n"+
    			"on yRespCostSummary1.FProjectID=pj.FID\n"+
    			"left join \n"+
    			"(select sum(FORIGINALAMOUNT) as qtgcf,FProjectID from T_PER_PeriodCostBill\n"+
    			"inner join T_BD_Period on T_PER_PeriodCostBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)yRespCostSummary2\n"+
    			"on yRespCostSummary2.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FORIGINALAMOUNT) as qtgcf,FProjectID from T_PER_IndirectCostBill\n"+
    			"inner join T_BD_Period on T_PER_IndirectCostBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)yRespCostSummary3\n"+
    			"on yRespCostSummary3.FProjectID=pj.FID\n"+
    			"left join\n"+

    			"(select sum(FCostAmount) as xmgllr ,FProjectID from T_PER_RespCostSumEntry inner join T_PER_RespCostSummary \n"+
    			"on T_PER_RespCostSummary.FID=T_PER_RespCostSumEntry.FBillID\n"+
    			"inner join T_BD_Period on T_PER_RespCostSummary.FPeriodID=T_BD_Period.FID\n"+
    			"where T_PER_RespCostSumEntry.FNumber='项目管理利润' and\n"+
    			" T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)yRespCostSummary4\n"+
    			"on yRespCostSummary4.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(T_EC_ProjectFillCbsSemiEntry.FAMOUNT) as bcp ,T_EC_ProjectFillCbsBill.FProjectID from  T_EC_ProjectFillCbsSemiEntry inner join T_EC_ProjectFillCbsBill  \n"+
    			"         on T_EC_ProjectFillCbsBill.FID=T_EC_ProjectFillCbsSemiEntry.fparentid \n"+
    			"        inner join T_BD_Period on T_EC_ProjectFillCbsBill.FPeriodID=T_BD_Period.FID \n"+
    			"where \n"+
    			" T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and T_EC_ProjectFillCbsBill.FBillSate='03'\n"+
    			"group by T_EC_ProjectFillCbsBill.FProjectID)yRespCostSummary5\n"+
    			"on yRespCostSummary5.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(fdifamount) as cltc ,FProjectID from T_EC_MaterialGapBillEntry inner join T_EC_MaterialGapBill \n"+
    			"on T_EC_MaterialGapBill.FID=T_EC_MaterialGapBillEntry.fparentid\n"+
    			"inner join T_BD_Period on T_EC_MaterialGapBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)yRespCostSummary6\n"+
    			"on yRespCostSummary6.FProjectID=pj.FID\n"+
    			"left join \n"+
    			"(select sum(FORIGINALAMOUNT) as gcsj,FProjectID from T_PER_RespCostEngineerTax\n"+
    			"inner join T_BD_Period on T_PER_RespCostEngineerTax.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)yRespCostSummary7\n"+
    			"on yRespCostSummary7.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FHumanAmount) as lwf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)yRealDirect1\n"+
    			"on yRealDirect1.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FMaterialAmount)+SUM(FSpordicAmount) as clf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)yRealDirect2\n"+
    			"on pj.FID=yRealDirect2.FProjectID\n"+
    			"left join\n"+
    			"(select SUM(FEquipmentAmount) as jxf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)yRealDirect3\n"+
    			"on yRealDirect3.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FOriginalAmount) as qtgcf,FProjectID from T_EC_RealOtherBill\n"+
    			"inner join T_BD_Period on T_EC_RealOtherBill.FPeriodID=T_BD_Period.fid\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID\n"+
    			")yRealOtherBill\n"+
    			"on yRealOtherBill.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FTaxation) as gcsj,FProjectID from T_EC_RealTaxBill\n"+
    			"inner join T_BD_Period on T_EC_RealTaxBill.FPeriodID=T_BD_Period.fid\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			" group by FProjectID\n"+
    			")yRealOtherBill4\n"+
    			"on yRealOtherBill4.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FTurnAmount) as zcf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)yzhoucaifei\n"+
    			"on yzhoucaifei.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FExpenseAmount) as qtf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)ydiangongdan\n"+
    			"on ydiangongdan.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FOriginalAmount) as jjf,FProjectID from T_EC_RealInDirectBill\n"+
    			"inner join T_BD_Period on T_EC_RealInDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)yRealInDirect\n"+
    			"on pj.FID=yRealInDirect.FProjectID\n"+
    			"left join\n"+

    			"(select SUM(fsubprice) as jbcc,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.FPeriodYear=substr('202007',1,4) and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)yjiabocaicha\n"+
    			"on yjiabocaicha.FProjectID=pj.FID\n"+
    			"left join \n"+
    			"(select sum(FORIGINALAMOUNT) as zjgcf,FProjectID from T_PER_DirectRespCost\n"+
    			"inner join T_BD_Period on T_PER_DirectRespCost.FPeriodID=T_BD_Period.FID\n"+
    			"where  T_BD_Period.Fnumber<='202007' and FBillSate='03'\n"+
    			"group by FProjectID)kRespCostSummary1\n"+
    			"on kRespCostSummary1.FProjectID=pj.FID\n"+
    			"left join \n"+
    			"(select sum(FORIGINALAMOUNT) as qtgcf,FProjectID from T_PER_PeriodCostBill\n"+
    			"inner join T_BD_Period on T_PER_PeriodCostBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kRespCostSummary2\n"+
    			"on kRespCostSummary2.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FORIGINALAMOUNT) as qtgcf,FProjectID from T_PER_IndirectCostBill\n"+
    			"inner join T_BD_Period on T_PER_IndirectCostBill.FPeriodID=T_BD_Period.FID\n"+
    			"where  T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kRespCostSummary3\n"+
    			"on kRespCostSummary3.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FCostAmount) as xmgllr ,FProjectID from T_PER_RespCostSumEntry inner join T_PER_RespCostSummary \n"+
    			"on T_PER_RespCostSummary.FID=T_PER_RespCostSumEntry.FBillID\n"+
    			"inner join T_BD_Period on T_PER_RespCostSummary.FPeriodID=T_BD_Period.FID\n"+
    			"where T_PER_RespCostSumEntry.FNumber='项目管理利润' and T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kRespCostSummary4\n"+
    			"on kRespCostSummary4.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(T_EC_ProjectFillCbsSemiEntry.FAMOUNT) as bcp ,T_EC_ProjectFillCbsBill.FProjectID from  T_EC_ProjectFillCbsSemiEntry inner join T_EC_ProjectFillCbsBill  \n"+
    			        " on T_EC_ProjectFillCbsBill.FID=T_EC_ProjectFillCbsSemiEntry.fparentid \n"+
    			        "inner join T_BD_Period on T_EC_ProjectFillCbsBill.FPeriodID=T_BD_Period.FID \n"+
    			"where T_BD_Period.Fnumber<='202007'  and T_EC_ProjectFillCbsBill.FBillSate='03'\n"+
    			"group by T_EC_ProjectFillCbsBill.FProjectID)kRespCostSummary5\n"+
    			"on kRespCostSummary5.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(fdifamount) as cltc ,FProjectID from T_EC_MaterialGapBillEntry inner join T_EC_MaterialGapBill \n"+
    			"on T_EC_MaterialGapBill.FID=T_EC_MaterialGapBillEntry.fparentid\n"+
    			"inner join T_BD_Period on T_EC_MaterialGapBill.FPeriodID=T_BD_Period.FID\n"+
    			"where  T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kRespCostSummary6\n"+
    			"on kRespCostSummary6.FProjectID=pj.FID\n"+
    			"left join \n"+
    			"(select sum(FORIGINALAMOUNT) as gcsj,FProjectID from T_PER_RespCostEngineerTax\n"+
    			"inner join T_BD_Period on T_PER_RespCostEngineerTax.FPeriodID=T_BD_Period.FID\n"+
    			"where  T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kRespCostSummary7\n"+
    			"on kRespCostSummary7.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FHumanAmount) as lwf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kRealDirect1\n"+
    			"on kRealDirect1.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FMaterialAmount)+SUM(FSpordicAmount) as clf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kRealDirect2\n"+
    			"on pj.FID=kRealDirect2.FProjectID\n"+
    			"left join\n"+
    			"(select SUM(FEquipmentAmount) as jxf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kRealDirect3\n"+
    			"on kRealDirect3.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FOriginalAmount) as qtgcf,FProjectID from T_EC_RealOtherBill\n"+
    			"inner join T_BD_Period on T_EC_RealOtherBill.FPeriodID=T_BD_Period.fid\n"+
    			"where T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID\n"+
    			")kRealOtherBill\n"+
    			"on kRealOtherBill.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FTaxation) as gcsj,FProjectID from T_EC_RealTaxBill\n"+
    			"inner join T_BD_Period on T_EC_RealTaxBill.FPeriodID=T_BD_Period.fid\n"+
    			"where T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			" group by FProjectID\n"+
    			")kRealOtherBill4\n"+
    			"on kRealOtherBill4.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FTurnAmount) as zcf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kzhoucaifei\n"+
    			"on kzhoucaifei.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select SUM(FExpenseAmount) as qtf,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kdiangongdan\n"+
    			"on kdiangongdan.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select sum(FOriginalAmount) as jjf,FProjectID from T_EC_RealInDirectBill\n"+
    			"inner join T_BD_Period on T_EC_RealInDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kRealInDirect\n"+
    			"on pj.FID=kRealInDirect.FProjectID\n"+
    			"left join\n"+
    			"(select SUM(fsubprice) as jbcc,FProjectID from T_EC_RealDirectEntry\n"+
    			"inner join T_EC_RealDirectBill on T_EC_RealDirectBill.FID=T_EC_RealDirectEntry.FBillID\n"+
    			"inner join T_BD_Period on T_EC_RealDirectBill.FPeriodID=T_BD_Period.FID\n"+
    			"where T_BD_Period.Fnumber<='202007'  and FBillSate='03'\n"+
    			"group by FProjectID)kjiabocaicha\n"+
    			"on kjiabocaicha.FProjectID=pj.FID\n"+
    			"left join\n"+
    			"(select  tpj.FID cfxmjcid,sum(mee.cfbqyj) bqyj,sum(mee.cfkdkzbj) zbj,sum(mee.cfbqsj) bqsj,sum(mee.CFBqqj) bqqj from CT_BIL_ManagementExpense me\n"+
    			"left join CT_BIL_ManagementExpenseentry mee on mee.fparentid=me.fid\n"+
    			"left join T_EC_Project tpj on tpj.FPROJECTORGID =mee.cforgid\n"+
    			"left join T_BD_Period c on c.fid=me.cfperiodid\n"+
    			"where cfdjstate >=02 and cfprojectstate=01 and c.fnumber ='202007' \n"+
    			"group by tpj.FID ) bqglf on bqglf.cfxmjcid=pj.FID\n"+
    			"left join\n"+
    			"(select  tpj.FID cfxmjcid,sum(mee.cfbqyj) bqyj,sum(mee.cfkdkzbj) zbj,sum(mee.cfbqsj) bqsj,sum(mee.CFBqqj) bqqj from CT_BIL_ManagementExpense me\n"+
    			"left join CT_BIL_ManagementExpenseentry mee on mee.fparentid=me.fid\n"+
    			"left join T_EC_Project tpj on tpj.FPROJECTORGID =mee.cforgid\n"+
    			"left join T_BD_Period c on c.fid=me.cfperiodid\n"+
    			"where cfdjstate >=02 and cfprojectstate=01 and c.FPeriodYear >=substr('202007',1,4)  and c.fnumber <='202007' \n"+
    			"group by tpj.FID )nlglf on nlglf.cfxmjcid=pj.FID\n"+
    			"left join\n"+
    			"(select  tpj.FID cfxmjcid,sum(mee.cfbqyj) bqyj,sum(mee.cfkdkzbj) zbj,sum(mee.cfbqsj) bqsj,sum(mee.CFBqqj) bqqj from CT_BIL_ManagementExpense me\n"+
    			"left join CT_BIL_ManagementExpenseentry mee on mee.fparentid=me.fid\n"+
    			"left join T_EC_Project tpj on tpj.FPROJECTORGID =mee.cforgid\n"+
    			"left join T_BD_Period c on c.fid=me.cfperiodid\n"+
    			"where cfdjstate >=02 and cfprojectstate=01 and c.FPeriodYear <='202007' \n"+
    			"group by tpj.FID ) klglf on klglf.cfxmjcid=pj.FID\n"+
    			"where org.flongnumber like '"+orgNum+"'  and pj.fname_l2 not like '%本部'\n"+
    			"order by org.FLongNumber ,pj.FName_l2\n"+
    			") group by gsbm,gsmc\n"+
    			"order by "+sortName+" desc";
//    	System.out.println(sql);
    	return sql;
    }

    @RequestMapping(value = "create", method = RequestMethod.GET)
    public String create(Model model, HttpServletRequest request, HttpServletResponse response) {
        if (!model.containsAttribute("data")) {
            model.addAttribute("data", newModel());
        }
        return display("edit");
    }

    @RequestMapping(value = "create", method = RequestMethod.POST)
    @ResponseBody
    public AjaxJson create(Model model, @Valid @ModelAttribute("data") SingleTable singleTable, BindingResult result,
                           HttpServletRequest request, HttpServletResponse response) {
        return doSave(singleTable, request, response, result);
    }

    @RequestMapping(value = "{id}/update", method = RequestMethod.GET)
    public String update(@PathVariable("id") String id, Model model, HttpServletRequest request,
                              HttpServletResponse response) {
        SingleTable singleTable = get(id);
        model.addAttribute("data", singleTable);
        return display("edit");
    }

    @RequestMapping(value = "{id}/update", method = RequestMethod.POST)
    @ResponseBody
    public AjaxJson update(Model model, @Valid @ModelAttribute("data") SingleTable singleTable, BindingResult result,
                           HttpServletRequest request, HttpServletResponse response) {
        return doSave(singleTable, request, response, result);
    }

    @RequestMapping(value = "/save", method = RequestMethod.POST)
    @ResponseBody
    public AjaxJson doSave(SingleTable singleTable, HttpServletRequest request, HttpServletResponse response,
                           BindingResult result) {
        AjaxJson ajaxJson = new AjaxJson();
        ajaxJson.success("保存成功");
        if (hasError(singleTable, result)) {
            // 错误提示
            String errorMsg = errorMsg(result);
            if (!StringUtils.isEmpty(errorMsg)) {
                ajaxJson.fail(errorMsg);
            } else {
                ajaxJson.fail("保存失败");
            }
            return ajaxJson;
        }
        try {
            if (StringUtils.isEmpty(singleTable.getId())) {
                singleTableService.insert(singleTable);
            } else {
                singleTableService.insertOrUpdate(singleTable);
            }
        } catch (Exception e) {
            e.printStackTrace();
            ajaxJson.fail("保存失败!<br />原因:" + e.getMessage());
        }
        return ajaxJson;
    }

    @RequestMapping(value = "{id}/delete", method = RequestMethod.POST)
    @ResponseBody
    public AjaxJson delete(@PathVariable("id") String id) {
        AjaxJson ajaxJson = new AjaxJson();
        ajaxJson.success("删除成功");
        try {
            singleTableService.deleteById(id);
        } catch (Exception e) {
            e.printStackTrace();
            ajaxJson.fail("删除失败");
        }
        return ajaxJson;
    }

    @RequestMapping(value = "batch/delete", method = { RequestMethod.GET, RequestMethod.POST })
    @ResponseBody
    public AjaxJson batchDelete(@RequestParam(value = "ids", required = false) String[] ids) {
        AjaxJson ajaxJson = new AjaxJson();
        ajaxJson.success("删除成功");
        try {
            List<String> idList = java.util.Arrays.asList(ids);
            singleTableService.deleteBatchIds(idList);
        } catch (Exception e) {
            e.printStackTrace();
            ajaxJson.fail("删除失败");
        }
        return ajaxJson;
    }

    @RequestMapping(value = "/{id}", method = RequestMethod.GET)
    public String view(Model model, @PathVariable("id") String id, HttpServletRequest request,
                       HttpServletResponse response) {
        SingleTable singleTable = get(id);
        model.addAttribute("data", singleTable);
        return display("edit");
    }

    @RequestMapping(value = "validate", method = { RequestMethod.GET, RequestMethod.POST })
    @ResponseBody
    public ValidJson validate(DuplicateValid duplicateValid, HttpServletRequest request) {
        ValidJson validJson = new ValidJson();
        Boolean valid = Boolean.FALSE;
        try {
            EntityWrapper<SingleTable> entityWrapper = new EntityWrapper<SingleTable>(entityClass);
            valid = singleTableService.doValid(duplicateValid,entityWrapper);
            if (valid) {
                validJson.setStatus("y");
                validJson.setInfo("验证通过!");
            } else {
                validJson.setStatus("n");
                if (!StringUtils.isEmpty(duplicateValid.getErrorMsg())) {
                    validJson.setInfo(duplicateValid.getErrorMsg());
                } else {
                    validJson.setInfo("当前信息重复!");
                }
            }
        } catch (Exception e) {
            validJson.setStatus("n");
            validJson.setInfo("验证异常，请检查字段是否正确!");
        }
        return validJson;
    }
}
